# import pytz
from flask import Flask, request, jsonify, render_template
# import pymysql
# import pymysql.cursors
# from apscheduler.schedulers.background import BackgroundScheduler
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import text


# Flask应用配置
app = Flask(__name__)

# 数据库配置
db_config = {
    "host": "localhost",
    "user": "root",
    "password": "123456",
    "db": "digital_twin_local",
}

app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:123456@localhost/digital_twin_local'
app.config['SQLALCHEMY_POOL_SIZE'] = 5
app.config['SQLALCHEMY_POOL_TIMEOUT'] = 30
app.config['SQLALCHEMY_POOL_RECYCLE'] = 3600
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['JSON_AS_ASCII'] = False  # 解决中文乱码问题
app.config['JSON_SORT_KEYS'] = False  # 解决jsonify排序问题
db = SQLAlchemy(app)


def connect_to_database():
    # connection = pymysql.connect(cursorclass=pymysql.cursors.DictCursor, **db_config)
    # return connection
    # 使用SQLAlchemy引擎创建连接
    return db.engine.connect()

# 添加在现有数据库函数之后
def get_system_data():
    try:
        sql = text("""
            SELECT 
                detection_time,
                CAST(temperature AS DECIMAL(10,2)) as temperature,
                CAST(noise AS DECIMAL(10,1)) as noise,
                CAST(input_voltage AS DECIMAL(10,2)) as input_voltage,
                CAST(output_voltage AS DECIMAL(10,2)) as output_voltage,
                CAST(humidity AS DECIMAL(5,2)) as humidity,
                system_id
            FROM (
                SELECT *,
                    ROW_NUMBER() OVER (PARTITION BY system_id ORDER BY detection_time DESC) as rn
                FROM system_data
            ) as subquery
            WHERE rn = 1
        """)
        result = db.session.execute(sql)
        return [dict(row) for row in result.mappings()]
    except Exception as e:
        app.logger.error(f"系统数据查询失败: {str(e)}")
        return []


def get_data():
    try:
        result = db.session.execute(
            text("select * from tb_result ORDER BY timetamp DESC LIMIT 9")
        )
        data_list = []
        for row in result:
            data_list.append(dict(row._mapping))  # 使用 row._mapping 获取字典
        return data_list
    finally:
        pass

def get_image_data(type):
    try:
        result = db.session.execute(
            text("select * from image_message where type = :type order by timestamp desc limit 4;"),
            {"type" : type}
        )
        data_list = []
        for row in result:
            data_list.append(dict(row._mapping))  # 使用 row._mapping 获取字典
        return data_list
    finally:
        pass


def get_new_data():
    try:
        result = db.session.execute(
            text("SELECT * FROM tb_text ORDER BY timetamp DESC LIMIT 1")
        )
        data_list = []
        for row in result:
            data_list.append(dict(row._mapping))  # 使用 row._mapping 获取字典
        return data_list
    finally:
        pass

def get_temperature_sensor_data():
    try:
        result = db.session.execute(
            text("select * from temperature_sensor order by detection_time desc limit 1;")
        )
        data_list = []
        for row in result:
            data_list.append(dict(row._mapping))  # 使用 row._mapping 获取字典
        return data_list
    finally:
        pass

def get_noise_sensor_data():
    try:
        result = db.session.execute(
            text("select * from noise_sensor where num = 1 order by detection_time desc limit 1")
        )
        data_list = []
        for row in result:
            data_list.append(dict(row._mapping))  # 使用 row._mapping 获取字典
        return data_list
    finally:
        pass

def get_voltage_history():
    try:
        result = db.session.execute(
            text("select * from tb_text order by timetamp desc limit 10")
        )
        data_list = []
        for row in result:
            data_list.append(dict(row._mapping))  # 使用 row._mapping 获取字典
        return data_list
    finally:
        pass


# def get_system_data():
#     try:
#         result = db.session.execute(
#             text("select * from system_data order by timestamp desc limit 1")
#         )
#         data_list = []
#         for row in result:
#             data_list.append(dict(row._mapping))  # 使用 row._mapping 获取字典
#         return data_list
#     finally:
#         pass

@app.route('/')
def index():
    return render_template('index.html')


@app.route('/api/data', methods=['POST'])
def data():
    data_list = get_data()
    return jsonify(data_list)

@app.route('/api/image/<int:type>', methods=['POST'])
def image_data(type):
    print(type)
    data_list = get_image_data(type)
    array = []
    for image_data in data_list:
        temperature = image_data['temperature_value']
        array.append(temperature)
    result = {
        "data": data_list,
        "array": array
    }
    return jsonify(result)

@app.route('/api/new_data', methods=['POST'])
def new_data():
    data_list = get_new_data()
    return jsonify(data_list)

@app.route('/api/voltage_history', methods=['POST'])
def voltage_history():
    voltage_data = get_voltage_history()
    return jsonify(voltage_data)

@app.route('/api/findCurrentTemperature', methods=['POST'])
def temperature_sensor():
    return jsonify(get_temperature_sensor_data())

@app.route('/api/findCurrentNoise', methods=['POST'])
def noise_sensor():
    return jsonify(get_noise_sensor_data())


# @app.route('/api/kgs/system_data', methods=['GET'])
# def kgs_system_data():
#     return jsonify(get_system_data())


@app.route('/index.html')
def homePage():
    return render_template('index.html')


@app.route('/index2.html')
def index2Page():
    return render_template('index2.html')


@app.route('/detect.html')
def detectPage():
    return render_template('detect.html')


@app.route('/anoinv.html')
def anoinvPage():
    return render_template('anoinv.html')


@app.route('/inversion.html')
def inversionPage():
    return render_template('inversion.html')


@app.route('/generation.html')
def generationPage():
    return render_template('generation.html')


@app.route('/select_detect', methods=['POST'])
def detect():
    img_path = request.form.get('img_path')
    img_path = img_path.replace('\\', '/')

    try:
        sql = text("SELECT detect_image, detect_score FROM tb_result WHERE spectrogram8 = :img_path")
        result = db.session.execute(sql, {'img_path': img_path})
        data = [dict(row._mapping) for row in result]
        return jsonify(data), 200
    except Exception as e:
        app.logger.error(f"查询失败: {str(e)}")
        return jsonify({"error": "数据库查询失败"}), 500


@app.route('/select_anoinv', methods=['POST'])
def anoinv():
    img_path = request.form.get('img_path')
    img_path = img_path.replace('\\', '/')

    try:
        # 使用SQLAlchemy的text方法执行参数化查询
        sql = text("SELECT anoinv_score, inversion_score FROM tb_result WHERE spectrogram8 = :img_path")
        print(sql)
        result = db.session.execute(sql, {'img_path': img_path})
        data = [dict(row._mapping) for row in result]
        return jsonify(data), 200
    except Exception as e:
        app.logger.error(f"查询失败: {str(e)}")
        return jsonify({"error": "数据库查询失败"}), 500


# @app.route('/select_inversion', methods=['POST'])
# def inversion():
#     img_path = request.form.get('img_path')
#     img_path = img_path.replace('\\', '/')
#
#     connection = connect_to_database()
#     try:
#         with connection.cursor() as cursor:
#             sql = "select inversion_score from tb_result where spectrogram8 = '%s'" % (img_path)
#             print(sql)
#             cursor.execute(sql)
#             inversion_score = cursor.fetchall()
#             connection.close()
#     finally:
#         pass
#
#     return inversion_score


@app.route('/select_generation', methods=['POST'])
def generation():
    img_path = request.form.get('img_path')
    img_path = img_path.replace('\\', '/')

    try:
        # 使用参数化查询并映射结果
        sql = text("""
            SELECT generation_image, sum_image, generation_BG_image 
            FROM tb_result 
            WHERE spectrogram8 = :img_path
        """)
        result = db.session.execute(sql, {'img_path': img_path})
        data = [dict(row._mapping) for row in result]
        return jsonify(data)
    except Exception as e:
        app.logger.error(f"生成数据查询失败: {str(e)}")
        return jsonify({"error": "数据库操作失败"}), 500

    connection = connect_to_database()
    try:
        with connection.cursor() as cursor:
            sql = "select generation_image, sum_image,  generation_BG_image from tb_result where spectrogram8 = '%s'" % (img_path)
            print(sql)
            cursor.execute(sql)
            generation_image = cursor.fetchall()
            connection.close()
    finally:
        pass

    return generation_image

@app.route('/kgs.html')
def kgsPage():
    return render_template('kgs.html')

@app.route('/index3.html')
def index3Page():
    return render_template('index3.html')


# 添加在现有路由之后
class SystemDataSchema:
    def __init__(self, name, value, unit, system_id):
        self.name = name
        self.value = value
        self.unit = unit
        self.system_id = system_id  # 保持Java字段命名


@app.route('/api/kgs/system_data', methods=['GET'])
def handle_system_data():
    try:
        raw_data = get_system_data()
        processed_data = []

        for item in raw_data:
            system_id = str(item['system_id'])

            sensor_mapping = [
                ('温度', item.get('temperature'), '°C'),
                ('噪声', item.get('noise'), 'dB'),
                ('相对湿度', item.get('humidity'), '%'),
                ('输入电压', item.get('input_voltage'), 'V'),
                ('输出电压', item.get('output_voltage'), 'V')
            ]

            for name, value, unit in sensor_mapping:
                data_obj = SystemDataSchema(
                    name=name,
                    value=float(value) if value is not None else None,
                    unit=unit,
                    system_id=system_id
                )
                processed_data.append(data_obj.__dict__)

        return jsonify(processed_data)

    except Exception as e:
        app.logger.error(f"数据处理异常: {str(e)}")
        return jsonify({"error": "服务不可用"}), 500
# 主函数
if __name__ == '__main__':
    app.run(debug=True)
